/* $RCSFile: CRMImageDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/AddonNice.ImageSettings/DAL/CRMImageDB.cs $
 * $log$
 * Revision 7 2010/08/09 09:44:02  christian.surieux
 *   Added Log to cs sources files
 */

using System;
using System.Configuration;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlTypes;
using System.Xml;
using System.Data.SqlClient;
using System.Globalization;

using AddonNice.Configuration;
using AddonNice.Diagnostics;

using AddonNice.CRMImage;

namespace AddonNice.Settings.CRMImage 
{
	/// <summary>
	/// DB access layer for CRMImage class
	/// </summary>
	public class CRMImageDB : DBDataStore
	{

		/// <summary>
		/// The AddCRMImage function is used to ADD CRMImage to the Database
		/// </summary>
        public int AddCRMImage(Guid CrmImageId, int imageLibId, string Caption, int Category, string metadataXml, string shortDescription, string keywords,
            string CreatedByUser, DateTime CreatedDate, Byte[] ImagePic1, Byte[] ImagePic2) 
		{
			SqlCommand myCommand                =   new SqlCommand("dbo.adn_AddCRMImage", myConnection);
			myCommand.CommandType               =   CommandType.StoredProcedure;

			SqlParameter parameterImageId       =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Direction          =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterImageId);

            SqlParameter parameterimageLibId    =   new SqlParameter("@imageLibId", SqlDbType.Int);
            parameterimageLibId.Value           =   imageLibId;
            myCommand.Parameters.Add(parameterimageLibId);

            SqlParameter parameteradnImage       =   new SqlParameter(ImStr.ATCrmImageId, SqlDbType.UniqueIdentifier);
            parameteradnImage.Value              =   CrmImageId;
            myCommand.Parameters.Add(parameteradnImage);
            
			SqlParameter parameterCaption       =   new SqlParameter(ImStr.ATCaption, SqlDbType.NVarChar,256);
			parameterCaption.Value              =   Caption;
			myCommand.Parameters.Add(parameterCaption);

			SqlParameter parameterCategory      =   new SqlParameter(ImStr.ATCategory, SqlDbType.Int);
			parameterCategory.Value             =   Category;
			myCommand.Parameters.Add(parameterCategory);

            SqlParameter parameterKeywords      =   new SqlParameter(ImStr.ATKeywords, SqlDbType.NVarChar,256);
			parameterKeywords.Value             =   keywords;
			myCommand.Parameters.Add(parameterKeywords);

			SqlParameter parameterCreatedByUser =   new SqlParameter("@CreatedByUser", SqlDbType.NVarChar, 100);
			parameterCreatedByUser.Value        =   CreatedByUser;
			myCommand.Parameters.Add(parameterCreatedByUser);

			SqlParameter parameterCreatedDate   =   new SqlParameter("@CreatedDate", SqlDbType.DateTime);
			parameterCreatedDate.Value          =   CreatedDate;
			myCommand.Parameters.Add(parameterCreatedDate);
            
			SqlParameter parameterShortDescription =    new SqlParameter(ImStr.ATShortDescription, SqlDbType.NVarChar,512);
			parameterShortDescription.Value     =   shortDescription;
			myCommand.Parameters.Add(parameterShortDescription);

            SqlParameter parameterMetadataXml   =   new SqlParameter(ImStr.ATMetadataXml, SqlDbType.Xml);
			parameterMetadataXml.Value          =   metadataXml;
			myCommand.Parameters.Add(parameterMetadataXml);

            SqlParameter parameterImagePic1     =   new SqlParameter("@ImagePic1", SqlDbType.VarBinary,-1);
            parameterImagePic1.Value            =   ImagePic1;
            myCommand.Parameters.Add(parameterImagePic1);

            SqlParameter parameterImagePic2     =   new SqlParameter("@ImagePic2", SqlDbType.VarBinary,-1);
            parameterImagePic2.Value            =   ImagePic2;
            myCommand.Parameters.Add(parameterImagePic2);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException sexp)
            {
                 if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB AddCRMImage CrmImageId: {0}, Number: {1}, Procedure: {2}, ex: {3}", 
                        new object[]{CrmImageId, sexp.Number,sexp.Procedure,sexp}),CrmImageTrace.Sw.Info);
                throw;
            }
            catch (Exception exp)
            {
                 if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB AddCRMImage CrmImageId: {0}, ex : {1}", CrmImageId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }

			return Convert.ToInt32(parameterImageId.Value);
		}

		/// <summary>
		/// The GetCRMImage function is used to Get a single CRMImage
		/// from the database for display/edit
		/// </summary>
		public SqlDataReader GetCRMImage(int ImageId) 
		{
			SqlCommand myCommand            =   new SqlCommand("dbo.adn_GetCRMImage", myConnection);
			myCommand.CommandType           =   CommandType.StoredProcedure;

			SqlParameter parameterImageId   =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Value          =   ImageId;
			myCommand.Parameters.Add(parameterImageId);

            SqlDataReader result = null;
            try
            {
                myConnection.Open();
                result                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB CRMImageDB GEtSingleCRMImage ex : {0}", exp),CrmImageTrace.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
		}

        /// <summary>
        /// The GetCRMImageByEntityId function is used to Get a single CRMImage using the associated CRM Entity ID
        /// from the database for display/edit
        /// </summary>
        public SqlDataReader GetCRMImageByEntityId(Guid EntityId)
        {
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetCRMImageByEntityId", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterCrmImageId    =   new SqlParameter(ImStr.ATCrmImageId, SqlDbType.UniqueIdentifier);
            parameterCrmImageId.Value           =   EntityId;
            myCommand.Parameters.Add(parameterCrmImageId);

            SqlDataReader result                =   null;
            try
            {
                myConnection.Open();
                result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetCRMImageByEntityId ex : {0}", exp),CrmImageTrace.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }

        /// <summary>
        /// The GetAllCRMImages function is used to Get all CRMImages ordered by Entity ID
        /// from the database for display
        /// </summary>
        public SqlDataReader GetAllCRMImages(int ImageLibId)
        {
            SqlCommand myCommand            =   new SqlCommand("dbo.adn_GetAllCRMImages", myConnection);
            myCommand.CommandType           =   CommandType.StoredProcedure;

            SqlDataReader result            =   null;
            try
            {
                myConnection.Open();
                result                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetAllCRMImages ex : {0}", exp),CrmImageTrace.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }

        /// <summary>
        /// The GetAllCRMImagesExcluding function is used to Get all CRMImages ordered by Entity ID
        /// from the database for display excluding images from ImageBook with Id send as parameter
        /// </summary>
        public SqlDataReader GetAllCRMImagesEx(Guid CRMId,int ImageLibId)
        {
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetAllCRMImagesEx", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterCrmImgBookId  =   new SqlParameter(ImStr.ATCRMId, SqlDbType.UniqueIdentifier);
            parameterCrmImgBookId.Value         =   CRMId;
            myCommand.Parameters.Add(parameterCrmImgBookId);

            SqlParameter parameterimageLibId    =   new SqlParameter("@imageLibId", SqlDbType.Int);
            parameterimageLibId.Value           =   ImageLibId;
            myCommand.Parameters.Add(parameterimageLibId);

            SqlDataReader result = null;
            try
            {
                myConnection.Open();
                result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetAllCRMImagesExcluding ex : {0}", exp),CrmImageTrace.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }

        /// <summary>
        /// The GetAllCRMImages function is used to Get all CRMImages ordered by Entity ID
        /// from the database for display
        /// </summary>
        public SqlDataReader GetAllCRMImagesPaged(int imageLibId,int page, int recordsPerPage)
        {
            SqlConnection Cnx                   =   newConnection;
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetAllCRMImagesPaged", Cnx);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterimageLibId    =   new SqlParameter("@imageLibId", SqlDbType.Int);
            parameterimageLibId.Value           =   imageLibId;
            myCommand.Parameters.Add(parameterimageLibId);

            SqlParameter parameterPage          =   new SqlParameter(ImStr.ATPage, SqlDbType.Int, 4);
            parameterPage.Value                 =   page;
            myCommand.Parameters.Add(parameterPage);

            SqlParameter parameterRecordsPerPage =  new SqlParameter(ImStr.ATRecordsPerPage, SqlDbType.Int, 4);
            parameterRecordsPerPage.Value       =   recordsPerPage;
            myCommand.Parameters.Add(parameterRecordsPerPage);


            //  Execute the command
            SqlDataReader result = null;
            try
            {
                Cnx.Open();
                result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetAllCRMImagesPaged ex : {0}", exp),CrmImageTrace.Sw.Info);
                if (result != null)
                    result.Close();
                if ( Cnx.State == System.Data.ConnectionState.Open)
                    Cnx.Close();
                throw;
            }
            return result;
        }

        /// <summary>
        /// The GetAllCRMImagesEx function is used to Get all CRMImages filtering/not filtering on category and sorting with sortkey
        /// </summary>
        public SqlDataReader GetAllCRMImagesPagedEx(int imageLibId,int page, int recordsPerPage, int catId, string sortkey)
        {
            if ( CrmImageTrace.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CRMImageDB GetAllCRMImagesPagedEx BEG imageLibId: {0}, page: {1}, recordsPerPage: {2}, catId: {3}, sortkey: {4}",
                    new object[]{imageLibId,page,recordsPerPage,catId,sortkey}),CrmImageTrace.Sw.Info);

            SqlConnection Cnx                   =   myConnection;
            if ( Cnx.State == ConnectionState.Open )
                Cnx                             =   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetAllCRMImagesPagedEx", Cnx);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterimageLibId    =   new SqlParameter("@imageLibId", SqlDbType.Int);
            parameterimageLibId.Value           =   imageLibId;
            myCommand.Parameters.Add(parameterimageLibId);

            SqlParameter parameterPage          =   new SqlParameter(ImStr.ATPage, SqlDbType.Int);
            parameterPage.Value                 =   page;
            myCommand.Parameters.Add(parameterPage);

            SqlParameter parameterRecordsPerPage =  new SqlParameter(ImStr.ATRecordsPerPage, SqlDbType.Int);
            parameterRecordsPerPage.Value       =   recordsPerPage;
            myCommand.Parameters.Add(parameterRecordsPerPage);

            SqlParameter parameterCatId         =   new SqlParameter("@CatId", SqlDbType.Int);
            parameterCatId.Value                =   catId;
            myCommand.Parameters.Add(parameterCatId);

            SqlParameter parameterSortKey       =   new SqlParameter("@SortKey", SqlDbType.NVarChar,50);
            parameterSortKey.Value              =   sortkey;
            myCommand.Parameters.Add(parameterSortKey);


            //  Execute the command
            SqlDataReader result = null;
            try
            {
                Cnx.Open();
                result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetAllCRMImagesPagedEx ex: {0}", exp),CrmImageTrace.Sw.Info);
                if (result != null)
                    result.Close();
                if ( Cnx.State == System.Data.ConnectionState.Open)
                    Cnx.Close();
                throw;
            }
            return result;
        }

        /// <summary>
		/// The DeleteCRMImage function is used to remove CRMImage from the Database
		/// </summary>
		public void DeleteCRMImage(int ImageId)
		{
			SqlCommand myCommand                =   new SqlCommand("dbo.adn_DeleteCRMImage", myConnection);
			myCommand.CommandType               =   CommandType.StoredProcedure;

			//  Add Parameters to SPROC
			SqlParameter parameterImageId       =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Value              =   ImageId;
			myCommand.Parameters.Add(parameterImageId);

			//  Execute the command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB DeleteCRMImage ImageId: {0}, ex : {1}", ImageId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
     
        }

		/// <summary>
		/// The UpdateCRMImage function is used to update changes to the CRMImage when associated file has changed
		/// </summary>
        public void UpdateCRMImage(int ImageId,int imageLibId, Guid CrmImageId, string Caption, int Category,string UpdatedByUser, string metadataXml, string shortDescription, string keywords, Byte[] ImagePic1, Byte[] ImagePic2) 
		{
			SqlCommand myCommand                =   new SqlCommand("dbo.adn_UpdateCRMImage", myConnection);
			myCommand.CommandType               =   CommandType.StoredProcedure;

			SqlParameter parameterImageId       =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Value              =   ImageId;
			myCommand.Parameters.Add(parameterImageId);

            SqlParameter parameterimageLibId    =   new SqlParameter("@imageLibId", SqlDbType.Int);
            parameterimageLibId.Value           =   imageLibId;
            myCommand.Parameters.Add(parameterimageLibId);

            SqlParameter parameteradnImage       =   new SqlParameter(ImStr.ATCrmImageId, SqlDbType.UniqueIdentifier);
            parameteradnImage.Value              =   CrmImageId;
            myCommand.Parameters.Add(parameteradnImage);

			SqlParameter parameterCaption       =   new SqlParameter(ImStr.ATCaption, SqlDbType.NVarChar,256);
			parameterCaption.Value              =   Caption;
			myCommand.Parameters.Add(parameterCaption);

			SqlParameter parameterCategory      =   new SqlParameter(ImStr.ATCategory, SqlDbType.Int);
			parameterCategory.Value             =   Category;
			myCommand.Parameters.Add(parameterCategory);

			SqlParameter parameterKeywords      =   new SqlParameter(ImStr.ATKeywords, SqlDbType.NVarChar,256);
			parameterKeywords.Value             =   keywords;
			myCommand.Parameters.Add(parameterKeywords);

            SqlParameter parameterUpdatedByUser =   new SqlParameter("@UpdatedByUser", SqlDbType.NVarChar,100);
			parameterUpdatedByUser.Value        =   UpdatedByUser;
			myCommand.Parameters.Add(parameterUpdatedByUser);

			SqlParameter parameterShortDescription =    new SqlParameter(ImStr.ATShortDescription, SqlDbType.NVarChar,512);
			parameterShortDescription.Value     =   shortDescription;
			myCommand.Parameters.Add(parameterShortDescription);

            SqlParameter parameterMetadataXml   =   new SqlParameter(ImStr.ATMetadataXml, SqlDbType.Xml);
			parameterMetadataXml.Value          =   metadataXml;
			myCommand.Parameters.Add(parameterMetadataXml);

            SqlParameter parameterImagePic1     =   new SqlParameter("@ImagePic1", SqlDbType.VarBinary,-1);
            parameterImagePic1.Value            =   ImagePic1;
            myCommand.Parameters.Add(parameterImagePic1);

            SqlParameter parameterImagePic2     =   new SqlParameter("@ImagePic2", SqlDbType.VarBinary,-1);
            parameterImagePic2.Value            =   ImagePic2;
            myCommand.Parameters.Add(parameterImagePic2);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB UpdateCRMImage ImageId: {0}, ex : {1}", ImageId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
        }

		/// <summary>
		/// The UpdateCRMImageData function is used to update changes to the CRMImage when associated file has NOT changed
		/// </summary>
        public void UpdateCRMImageData(int ImageId, string Caption, int Category,string UpdatedByUser,string metadataXml, string shortDescription, string keywords) 
		{
			SqlCommand myCommand                =   new SqlCommand("dbo.adn_UpdateCRMImageData", myConnection);
			myCommand.CommandType               =   CommandType.StoredProcedure;

			//  Add Parameters to SPROC
			SqlParameter parameterImageId       =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Value              =   ImageId;
			myCommand.Parameters.Add(parameterImageId);

			SqlParameter parameterCaption       =   new SqlParameter(ImStr.ATCaption, SqlDbType.NVarChar,256);
			parameterCaption.Value              =   Caption;
			myCommand.Parameters.Add(parameterCaption);

			SqlParameter parameterCategory      =   new SqlParameter(ImStr.ATCategory, SqlDbType.Int);
			parameterCategory.Value             =   Category;
			myCommand.Parameters.Add(parameterCategory);

            SqlParameter parameterMetadataXml   =   new SqlParameter(ImStr.ATMetadataXml, SqlDbType.Xml);
			parameterMetadataXml.Value          =   metadataXml;
			myCommand.Parameters.Add(parameterMetadataXml);
            
            SqlParameter parameterUpdatedByUser =   new SqlParameter("@UpdatedByUser", SqlDbType.NVarChar,100);
			parameterUpdatedByUser.Value        =   UpdatedByUser;
			myCommand.Parameters.Add(parameterUpdatedByUser);

			SqlParameter parameterShortDescription = new SqlParameter(ImStr.ATShortDescription, SqlDbType.NVarChar,512);
			parameterShortDescription.Value     =   shortDescription;
			myCommand.Parameters.Add(parameterShortDescription);

			SqlParameter parameterKeywords      =   new SqlParameter(ImStr.ATKeywords, SqlDbType.NVarChar,256);
			parameterKeywords.Value             =   keywords;
			myCommand.Parameters.Add(parameterKeywords);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB UpdateCRMImageData ImageId: {0}, ex : {1}", ImageId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
        }

    
		/// <summary>
        /// The UpdateCRMImageBook function is used to update CRMImageBook to the Database
		/// </summary>
        public void UpdateCRMImageBook(string EntityName, Guid CrmId,int ImageId,int OldImageId,int DisplayOrder,string metadataXml, string shortDescription) 
		{
			SqlCommand myCommand                = new SqlCommand("dbo.adn_UpdateCRMImageBook", myConnection);
			myCommand.CommandType               = CommandType.StoredProcedure;


            SqlParameter parameterEntityName    = new SqlParameter(ImStr.ATEntityName, SqlDbType.NChar,50);
			parameterEntityName.Value           = EntityName;
			myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameteradnImage       = new SqlParameter(ImStr.ATCRMId, SqlDbType.UniqueIdentifier);
            parameteradnImage.Value              = CrmId;
            myCommand.Parameters.Add(parameteradnImage);
            
			SqlParameter parameterImageId       = new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Value              = ImageId;
			myCommand.Parameters.Add(parameterImageId);

			SqlParameter parameterOldImageId    = new SqlParameter("@OldImageId", SqlDbType.Int, 4);
			parameterOldImageId.Value           = OldImageId;
			myCommand.Parameters.Add(parameterOldImageId);

			SqlParameter parameterDisplayOrder  = new SqlParameter(ImStr.ATDisplayOrder, SqlDbType.Int,4);
			parameterDisplayOrder.Value         = DisplayOrder;
			myCommand.Parameters.Add(parameterDisplayOrder);

			SqlParameter parameterShortDescription =    new SqlParameter(ImStr.ATShortDescription, SqlDbType.NVarChar,512);
			parameterShortDescription.Value     =   shortDescription;
			myCommand.Parameters.Add(parameterShortDescription);

            SqlParameter parameterMetadataXml   =   new SqlParameter(ImStr.ATMetadataXml, SqlDbType.Xml);
			parameterMetadataXml.Value          =   metadataXml;
			myCommand.Parameters.Add(parameterMetadataXml);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB UpdateCRMImageBook ImageId: {0}, ex : {1}", ImageId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
		}

		/// <summary>
		/// The AddCRMImage function is used to ADD CRMImage to the Database
		/// </summary>
        public void AddCRMImageBook(string EntityName, Guid CrmId,int ImageId, int DisplayOrder,string metadataXml, string shortDescription) 
		{
            SqlConnection   theConnection       =   newConnection;
			SqlCommand myCommand                =   new SqlCommand("dbo.adn_AddCRMImageBook", theConnection);
			myCommand.CommandType               =   CommandType.StoredProcedure;


            SqlParameter parameterEntityName    =  new SqlParameter(ImStr.ATEntityName, SqlDbType.NChar,50);
			parameterEntityName.Value           =   EntityName;
			myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameteradnImage       =   new SqlParameter("@CrmId", SqlDbType.UniqueIdentifier);
            parameteradnImage.Value              =   CrmId;
            myCommand.Parameters.Add(parameteradnImage);
            
			SqlParameter parameterImageId       =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Value              =   ImageId;
			myCommand.Parameters.Add(parameterImageId);

			SqlParameter parameterDisplayOrder  = new SqlParameter(ImStr.ATDisplayOrder, SqlDbType.Int,4);
			parameterDisplayOrder.Value         =   DisplayOrder;
			myCommand.Parameters.Add(parameterDisplayOrder);

			SqlParameter parameterShortDescription =    new SqlParameter(ImStr.ATShortDescription, SqlDbType.NVarChar,512);
			parameterShortDescription.Value     =   shortDescription;
			myCommand.Parameters.Add(parameterShortDescription);

            SqlParameter parameterMetadataXml   =   new SqlParameter(ImStr.ATMetadataXml, SqlDbType.Xml);
			parameterMetadataXml.Value          =   metadataXml;
			myCommand.Parameters.Add(parameterMetadataXml);

            try
            {
                theConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB AddCRMImageBook ImageId: {0}, ex : {1}", ImageId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
            }
		}


        /// <summary>
        /// The DeleteCRMImageBook function is used to Delete All CRMImage from a CRMImageBook
        /// </summary>
        public void DeleteAllCRMImageBook(string EntityName, Guid CrmId)
        {
            SqlCommand myCommand            =   new SqlCommand("dbo.adn_DeleteAllCRMImageBook", myConnection);
            myCommand.CommandType           =   CommandType.StoredProcedure;


            SqlParameter parameterEntityName =  new SqlParameter(ImStr.ATEntityName, SqlDbType.NChar, 50);
            parameterEntityName.Value       =   EntityName;
            myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameteradnImage   =   new SqlParameter("@CrmId", SqlDbType.UniqueIdentifier);
            parameteradnImage.Value          =   CrmId;
            myCommand.Parameters.Add(parameteradnImage);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB DeleteAllCRMImageBook CrmId: {0}-{1}, ex : {2}", EntityName,CrmId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
        }

        /// <summary>
		/// The DeleteCRMImageBook function is used to Delete One CRMImage from a CRMImageBook
		/// </summary>
        public void DeleteCRMImageBook(string EntityName, Guid CrmId,int ImageId) 
		{
			SqlCommand myCommand                =   new SqlCommand("dbo.adn_DeleteCRMImageBook", myConnection);
			myCommand.CommandType               =   CommandType.StoredProcedure;


            SqlParameter parameterEntityName    =   new SqlParameter(ImStr.ATEntityName, SqlDbType.NChar,50);
			parameterEntityName.Value           =   EntityName;
			myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameteradnImage       =   new SqlParameter("@CrmId", SqlDbType.UniqueIdentifier);
            parameteradnImage.Value              =   CrmId;
            myCommand.Parameters.Add(parameteradnImage);
            
			SqlParameter parameterImageId       =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
			parameterImageId.Value              =   ImageId;
			myCommand.Parameters.Add(parameterImageId);


            //  Execute the command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB DeleteCRMImageBook ImageId: {0}, ex : {1}", ImageId, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
		}

        /// <summary>
        /// The GetCRMImageBook function is used to Get a single CRMImage from an ImageBook
        /// from the database for display/edit
        /// </summary>
        public SqlDataReader GetSingleCRMImageBook(string EntityName,Guid EntityId,int ImageId)
        {
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetSingleCRMImageBook", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterEntityName    =   new SqlParameter(ImStr.ATEntityName, SqlDbType.VarChar, 50);
            parameterEntityName.Value           =   EntityName;
            myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameterEntityId      =   new SqlParameter(ImStr.ATCRMId, SqlDbType.UniqueIdentifier);
            parameterEntityId.Value             =   EntityId;
            myCommand.Parameters.Add(parameterEntityId);
            
            SqlParameter parameterImageId       =   new SqlParameter(ImStr.ATImageId, SqlDbType.Int, 4);
            parameterImageId.Value              =   ImageId;
            myCommand.Parameters.Add(parameterImageId);

            //  Execute the command
            SqlDataReader result = null;
            try
            {
                myConnection.Open();
                result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetSingleCRMImageBook ex : {0}", exp),CrmImageTrace.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }

        /// <summary>
		/// The GetCRMImageBook function is used to get all the CRMImage associated with a specific entity
        /// </summary>
		public DataSet GetCRMImageBook(string EntityName,Guid EntityId) 
		{
			SqlDataAdapter myCommand                =   new SqlDataAdapter("dbo.adn_GetCRMImageBook", myConnection);
			myCommand.SelectCommand.CommandType     =   CommandType.StoredProcedure;

			//  Add Parameters to SPROC
            SqlParameter parameterEntityName        =   new SqlParameter(ImStr.ATEntityName, SqlDbType.VarChar, 50);
            parameterEntityName.Value               =   EntityName;
            myCommand.SelectCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameterEntityId          =   new SqlParameter(ImStr.ATCRMId, SqlDbType.UniqueIdentifier);
            parameterEntityId.Value                 =   EntityId;
            myCommand.SelectCommand.Parameters.Add(parameterEntityId);

			//  Create and Fill the DataSet
			DataSet myDataSet                       =   new DataSet();
            try
            {
                myCommand.Fill(myDataSet);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetCRMImageBook EntityName: {0}, ex : {1}", EntityName, exp),CrmImageTrace.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
			return CheckDataSetForDates(myDataSet);
		}

        
        /// <summary>
		/// The GetCRMImageBookNb function is used to get the count of CRMImages records associated with a specific entity
        /// </summary>
		public SqlDataReader GetCRMImageBookNb(string EntityName,Guid EntityId) 
		{
			SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetCRMImageBookNb", myConnection);
			myCommand.CommandType               =   CommandType.StoredProcedure;

			//  Add Parameters to SPROC
            SqlParameter parameterEntityName    =   new SqlParameter(ImStr.ATEntityName, SqlDbType.VarChar, 50);
            parameterEntityName.Value           =   EntityName;
            myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameterEntityId      =   new SqlParameter(ImStr.ATCRMId, SqlDbType.UniqueIdentifier);
            parameterEntityId.Value             =   EntityId;
            myCommand.Parameters.Add(parameterEntityId);

			SqlDataReader dr                    =   null;
            try
            {
                myConnection.Open();
                dr                              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetCRMImageBookNb EntityName: {0}, EntityId: {1}, ex : {2}", EntityName, EntityId, exp),CrmImageTrace.Sw.Info);
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
			return dr;
		}


        /// <summary>
        /// The GetCRMAllImageBookPaged function is used to get all the CRMImage ordered by entity
        /// </summary>
        public SqlDataReader GetCRMAllImageBookPaged(int imageLibId,int page, int recordsPerPage)
        {
            SqlConnection Cnx                   =   newConnection;
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetCRMAllImageBookPaged", Cnx);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterimageLibId    =   new SqlParameter("@imageLibId", SqlDbType.Int);
            parameterimageLibId.Value           =   imageLibId;
            myCommand.Parameters.Add(parameterimageLibId);

            SqlParameter parameterPage          =   new SqlParameter(ImStr.ATPage, SqlDbType.Int, 4);
            parameterPage.Value                 =   page;
            myCommand.Parameters.Add(parameterPage);

            SqlParameter parameterRecordsPerPage =  new SqlParameter(ImStr.ATRecordsPerPage, SqlDbType.Int, 4);
            parameterRecordsPerPage.Value       =   recordsPerPage;
            myCommand.Parameters.Add(parameterRecordsPerPage);

            SqlDataReader dr                    =   null;
            try
            {
                Cnx.Open();
                dr                              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetCRMAllImageBookPaged ex : {0}", exp),CrmImageTrace.Sw.Info);
                if (dr != null)
                    dr.Close();
                if (Cnx.State == System.Data.ConnectionState.Open)
                    Cnx.Close();
                throw;
            }
            return dr;
        }


        /// <summary>
        /// The GetCRMImageBookFull function is used to get all the CRMImage associated with a specific entity
        /// </summary>
		public SqlDataReader GetCRMImageBookFull(string EntityName,Guid EntityId) 
		{
            if ( CrmImageTrace.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CRMImageDB GetCRMImageBookFull EntityName: {0}, EntityId: {1}.", EntityName,EntityId),CrmImageTrace.Sw.Info);
            SqlConnection Cnx                   =   newConnection;

            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetCRMImageBookFull", Cnx);
			myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterEntityName    =   new SqlParameter(ImStr.ATEntityName, SqlDbType.VarChar, 50);
            parameterEntityName.Value           =   EntityName;
            myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameterEntityId      =   new SqlParameter(ImStr.ATCRMId, SqlDbType.UniqueIdentifier);
            parameterEntityId.Value             =   EntityId;
            myCommand.Parameters.Add(parameterEntityId);


            SqlDataReader dr                    =   null;
            try
            {
                Cnx.Open();
                dr                              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetCRMImageBookFull EntityName: {0}, ex: {1}", EntityName, exp),CrmImageTrace.Sw.Info);
                if (dr != null)
                    dr.Close();
                if (Cnx.State == System.Data.ConnectionState.Open)
                    Cnx.Close();
                throw;
            }
            return dr;
		}   
 
        /// <summary>
        /// The GetCRMImageBookFullPaged function is used to get all the CRMImage associated with a specific entity
        /// </summary>
		public SqlDataReader GetCRMImageBookFullPaged(int page,int recordsPerPage,string EntityName,Guid EntityId) 
		{
            if ( CrmImageTrace.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CRMImageDB GetCRMImageBookFullPaged EntityName: {0}, EntityId: {1}, page: {2}, recordsPerPage: {3}.", 
                    new object[]{EntityName,EntityId,page,recordsPerPage}),CrmImageTrace.Sw.Info);

            SqlConnection Cnx                   =   newConnection;

            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetCRMImageBookFullPaged", Cnx);
			myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterEntityName    =   new SqlParameter(ImStr.ATEntityName, SqlDbType.VarChar, 50);
            parameterEntityName.Value           =   EntityName;
            myCommand.Parameters.Add(parameterEntityName);

            SqlParameter parameterEntityId      =   new SqlParameter(ImStr.ATCRMId, SqlDbType.UniqueIdentifier);
            parameterEntityId.Value             =   EntityId;
            myCommand.Parameters.Add(parameterEntityId);


            SqlParameter parameterPage          =   new SqlParameter(ImStr.ATPage, SqlDbType.Int, 4);
            parameterPage.Value                 =   page;
            myCommand.Parameters.Add(parameterPage);

            SqlParameter parameterRecordsPerPage =  new SqlParameter(ImStr.ATRecordsPerPage, SqlDbType.Int, 4);
            parameterRecordsPerPage.Value       =   recordsPerPage;
            myCommand.Parameters.Add(parameterRecordsPerPage);

            SqlDataReader dr                    =   null;
            try
            {
                Cnx.Open();
                dr                              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( CrmImageTrace.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CRMImageDB GetCRMImageBookFull EntityName: {0}, ex: {1}", EntityName, exp),CrmImageTrace.Sw.Info);
                if (dr != null)
                    dr.Close();
                if (Cnx.State == System.Data.ConnectionState.Open)
                    Cnx.Close();
                throw;
            }
            return dr;
		}   
     }
}